package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import main.DataTransferObject;
import main.MyConnection;
import service.LocationService;

public class LocationDAOImpl implements LocationDAO {

	@Override
	public boolean createOrUpdate(DataTransferObject inDTO) {
		
		try{
			Connection conn = MyConnection.getConnection();
			String sql = "SELECT * FROM Location WHERE Loc_IP = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, inDTO.getIp());
			ps.execute();
	
			ResultSet rs = ps.getResultSet();
			if(rs.next()){ 
				sql = "UPDATE Location SET Loc_Country = ?, Loc_City = ?, Loc_Area_Code = ? WHERE Loc_IP = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, inDTO.getCountry());
				ps.setString(2, inDTO.getCity());
				ps.setString(3, inDTO.getAreaCode());
				ps.setString(4, inDTO.getIp());
				ps.execute();
			} 
			else {
				sql = "INSERT INTO Location (Loc_IP,Loc_Country,Loc_City,Loc_Area_Code) VALUES " +
						"(?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, inDTO.getIp());
				ps.setString(2, inDTO.getCountry());
				ps.setString(3, inDTO.getCity());
				ps.setString(4, inDTO.getAreaCode());
				ps.execute();
			}
		} catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean createOrUpdate(String ip) {
		try{
			Connection conn = MyConnection.getConnection();
			String sql = "SELECT * FROM Location WHERE Loc_IP = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, ip);
			ps.execute();
	
			ResultSet rs = ps.getResultSet();
			if(rs.next()){ 
				DataTransferObject inDTO = LocationService.getLocationDetails(ip);
				sql = "UPDATE Location SET Loc_Country = ?, Loc_City = ?, Loc_Area_Code = ? WHERE Loc_IP = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, inDTO.getCountry());
				ps.setString(2, inDTO.getCity());
				ps.setString(3, inDTO.getAreaCode());
				ps.setString(4, ip);
				ps.execute();
			} 
			else {
				DataTransferObject inDTO = LocationService.getLocationDetails(ip);
				sql = "INSERT INTO Location (Loc_IP,Loc_Country,Loc_City,Loc_Area_Code) VALUES " +
						"(?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, ip);
				ps.setString(2, inDTO.getCountry());
				ps.setString(3, inDTO.getCity());
				ps.setString(4, inDTO.getAreaCode());
				ps.execute();
			}
		} catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public DataTransferObject getLocationFromIP(String ip) {
		DataTransferObject dto = null;
		try{
			Connection conn = MyConnection.getConnection();
			String sql = "SELECT * FROM Location WHERE Loc_IP = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, ip);
			ps.execute();
	
			ResultSet rs = ps.getResultSet();
			if(rs.next()){
				dto = new DataTransferObject();
				dto.setLocID(rs.getInt("Loc_ID"));
				dto.setCountry(rs.getString("Loc_Country"));
				dto.setCity(rs.getString("Loc_City"));
				dto.setAreaCode(rs.getString("Loc_Area_Code"));
			}
		} catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return dto;
	}

	@Override
	public List<DataTransferObject> getAllLocations() {
		ArrayList<DataTransferObject> locList = new ArrayList<DataTransferObject>();
		try{
			Connection conn = MyConnection.getConnection();
			String sql = "SELECT * FROM Location";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.execute();
	
			ResultSet rs = ps.getResultSet();
			while(rs.next()){
				DataTransferObject dto = new DataTransferObject();
				dto.setLocID(rs.getInt("Loc_ID"));
				dto.setCountry(rs.getString("Loc_Country"));
				dto.setCity(rs.getString("Loc_City"));
				dto.setAreaCode(rs.getString("Loc_Area_Code"));
				locList.add(dto);
			}
		} catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return locList;
	}
}
